package com.neusoft.chenhanhua.gqing;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import org.junit.Test;

public class Cms_prefrence_area {
	//插入
	@Test
	public void insert() {
		Connection conn=null;
		String username="root";
		String password="root";
		String driver="com.mysql.jdbc.Driver";
		String url="jdbc:mysql://localhost:3306/mall?useUnicode=true&charaterEncoding=utf-8";
		try {
			//加入驱动类
			Class.forName(driver);
			conn=DriverManager.getConnection(url,username,password);
		} catch (Exception e) {
			e.printStackTrace();
		}
		String sql="insert into Cms_prefrence_area(id,name,sub_title,pic,sort,show_status)values(9,'fd','dfd','dd0',3,4)";
		Statement st=null;
		try {
			//通过数据库连接conn获取事务java.sql.Statement对象st
			st=conn.createStatement();
			//执行sql命令
			int row=st.executeUpdate(sql);
			System.out.println(row>0?"添加成功":"添加失败");
		} catch (SQLException e) {
			e.printStackTrace();
		}finally {
			if (st!=null) {
				try {
					st.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			if (conn!=null) {
				try {
					conn.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
		}
	}
	//修改
	@Test
	public void update() {
		Connection conn=null;
		//准备数据库密码,用户名，url,driver
		String username="root";
		String passowrd="root";
		String driver="com.mysql.jdbc.Driver";
		String url="jdbc:mysql://localhost:3306/mall?useUnicode=true&characterEncoding=utf-8";
		try {
			//加载驱动类到虚拟机中
			Class.forName(driver);
			//连接数据库
			conn=DriverManager.getConnection(url,username,passowrd);
		} catch (Exception e) {
			e.printStackTrace();
		}
		//准备数据库命令id,name,sub_title,pic,sort,show_status
		String sql="update Cms_prefrence_area set name='你好 'where id=2";
		Statement st=null;
		try {
			//获取connjava.sql。statement对象st
			st=conn.createStatement();
			int row=st.executeUpdate(sql);
			System.out.println(row>0?"修改成功":"修改失败");
		} catch (SQLException e) {
			e.printStackTrace();
		}finally {
			if (conn!=null) {
				try {
					conn.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			if (st!=null) {
				try {
					st.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
		}
		
	}
	@Test
	public void delete() {
		Connection conn=null;
		String username="root";
		String passowrd="root";
		String driver="com.mysql.jdbc.Driver";
		String url="jdbc:mysql://localhost:3306/mall?useUnicode=true&charaterEncoding=utf-8";
	    try {
	    	//加载驱动类到虚拟机
			Class.forName(driver);
			conn=DriverManager.getConnection(url,username,passowrd);
		} catch (Exception e) {
			e.printStackTrace();
		}
	    //准备SQL命令
	    String sql="delete from Cms_prefrence_area where id=2";
	    Statement st=null;
	    try {
			st=conn.createStatement();
			int row=st.executeUpdate(sql);
			System.out.println(row>0?"删除成功":"删除失败");
		} catch (SQLException e) {
			e.printStackTrace();
		}finally {
			if (st!=null) {
				try {
					st.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			if (conn!=null) {
				try {
					conn.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
		}
	    
		
	}
	//查询查一条
		@Test
		public void selet() {
			Connection conn = null;
			//1、准备数据库用户名、密码、url、driver
			String username = "root";
			String password = "root";
			String driver = "com.mysql.jdbc.Driver";
			//协议://地址:端口号/数据库名?连接属性设置
			String url = "jdbc:mysql://localhost:3306/mall?useUnicode=true&characterEncoding=utf-8";
			
			try {
				//加载驱动类到虚拟机中
				Class.forName(driver);//这是属于反射的内容
				//获取数据库连接java.sql.Connection
				conn = DriverManager.getConnection(url, username, password);
			} catch (Exception e) {
				e.printStackTrace();
			}
			//准备SQL命令
					String sql = "select * from Cms_prefrence_area";
					Statement st = null;
					ResultSet rs = null;
					try {
						//通过数据库连接conn获取事务java.sql.Statement对象st
						st = conn.createStatement();
						//执行sql命令
						rs = st.executeQuery(sql);
						//处理查询结果集
						while(rs.next()) {
							//System.out.println(rs.getInt(1));
							//rs.getInt(columnLabel)：columnLabel列名或列的别名，推荐使用
							System.out.println(rs.getInt("id"));
							System.out.println(rs.getObject("name"));
							System.out.println(rs.getObject("sub_title"));
							System.out.println(rs.getObject("pic"));
							System.out.println(rs.getInt("sort"));
							System.out.println(rs.getInt("show_status"));
							//获取username列
							//System.out.println(rs.getString("username"));
						}
					} catch (SQLException e) {
						e.printStackTrace();
					} finally {
						if(rs != null) {
							try {
								rs.close();
							} catch (SQLException e) {
								e.printStackTrace();
							}
						}
						if(st != null) {
							try {
								st.close();
							} catch (SQLException e) {
								e.printStackTrace();
							}
						}
						if(conn != null) {
							try {
								conn.close();
							} catch (SQLException e) {
								e.printStackTrace();
							}
						}
		}
		}

}
